﻿using System;
using System.Collections.Generic;
using System.ComponentModel.DataAnnotations.Schema;
using System.Data;
using System.Data.SQLite;
using System.Linq;
using System.Reflection;
using System.Text;

namespace Learun.Util
{
    /// <summary>
    /// 
    /// </summary>
    public class BaseDAL
    {
        /// <summary>
        /// 添加记录
        /// </summary>
        /// <param name="list">ClaimList实体类集合</param>
        /// <param name="sqlitedbPath">sqlitedbPath</param>
        /// <returns>操作结果</returns>
        public bool Add<T>(List<T> list, string sqlitedbPath)
        {
            sqlitedbPath = sqlitedbPath.Replace('/', '\\');
            var ConStr = $"Data Source=" + sqlitedbPath;
            SqliteHelper.Start(ConStr);
            if (list == null || list.Count <= 0) return false;
            var sqlList = new List<string>();
            var paramList = new List<List<SQLiteParameter>>();

            string sql = GetSqlter_Insert<T>();

            foreach (var item in list)
            {
                var ParentType = item.GetType();
                System.Reflection.PropertyInfo[] Properties = ParentType.GetProperties();
                List<SQLiteParameter> parameters = new List<SQLiteParameter>();
                for (int i = 0; i < Properties.Count(); i++)
                {
                    System.Reflection.PropertyInfo propertyInfo = Properties[i];
                    if (!IsAttri<NotMappedAttribute>(propertyInfo))
                    {
                        var dbType = GetDbType(propertyInfo);
                        var sQLiteParameter = new SQLiteParameter($"@{propertyInfo.Name}", dbType) { Value = propertyInfo.GetValue(item, null) };
                        parameters.Add(sQLiteParameter);
                    }
                }

                sqlList.Add(sql);
                paramList.Add(parameters);
            }
            try
            {
                return SqliteHelper.SQLHelper_EF_Add(sqlList, paramList) == list.Count;
            }
            catch (System.Exception)
            {
                throw;
            }
        }

        /// <summary>
        /// 创建表
        /// </summary>
        /// <param name="sqlitedbPath"></param>
        /// <returns></returns>
        public bool Create<T>(string sqlitedbPath)
        {
            sqlitedbPath = sqlitedbPath.Replace('/', '\\');
            var ConStr = $"Data Source={sqlitedbPath}";
            SqliteHelper.Start(ConStr);
            List<string> sqlList = new List<string>();
            var ParentType = typeof(T);
            System.Reflection.PropertyInfo[] Properties = ParentType.GetProperties();
            int len = 1000 + Properties.Count() * 100;
            StringBuilder sqlBuilder = new StringBuilder(len);
            sqlBuilder.AppendLine($" CREATE TABLE {ParentType.Name.Replace("Entity","")}( ");

            string key = string.Empty;
            for (int i = 0; i < Properties.Count(); i++)
            {
                System.Reflection.PropertyInfo propertyInfo = Properties[i];
                if (IsAttri<PrimartyKeyAttribute>(propertyInfo))
                {
                    key = propertyInfo.Name;
                    string dbType = GetCreateTableDbType(propertyInfo);
                    string isNull = IsAttri<AllowNoNULLAttribute>(propertyInfo) ? "NOT NULL" : "NULL";
                    string defaultValue = IsDefualValue(propertyInfo);
                    sqlBuilder.AppendLine($" {propertyInfo.Name} {dbType} {isNull} {defaultValue} CONSTRAINT PK_{ParentType.Name} PRIMARY KEY, ");
                    break;
                }
            }

            for (int i = 0; i < Properties.Count(); i++)
            {
                System.Reflection.PropertyInfo propertyInfo = Properties[i];
                if (key == propertyInfo.Name) continue;
                if (!IsAttri<NotMappedAttribute>(propertyInfo))
                {
                    string dbType = GetCreateTableDbType(propertyInfo);
                    string isNull = IsAttri<AllowNoNULLAttribute>(propertyInfo) ? "NOT NULL" : "NULL";
                    string defaultValue = IsDefualValue(propertyInfo);
                    sqlBuilder.AppendLine($" {propertyInfo.Name} {dbType} {isNull} {defaultValue} , ");
                }
            }

            sqlList.Add(sqlBuilder.ToString().TrimEnd().TrimEnd(',') + "); ");
            try
            {
                return SqliteHelper.SQLHelper_EF_Add(sqlList) == 1;
            }
            catch (System.Exception)
            {
                throw;
            }
        }

        /// <summary>
        /// SQL插入语句
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <returns></returns>
        public string GetSqlter_Insert<T>()
        {
            var ParentType = typeof(T);
            string TableName = ParentType.Name.Replace("Entity", "");
            System.Reflection.PropertyInfo[] Properties = ParentType.GetProperties();
            StringBuilder stringBuilder1 = new StringBuilder();
            StringBuilder stringBuilder2 = new StringBuilder();

            int count = Properties.Count();

            for (int i = 0; i < Properties.Count(); i++)
            {
                System.Reflection.PropertyInfo propertyInfo = Properties[i];
                if (!IsAttri<NotMappedAttribute>(propertyInfo))
                {
                    stringBuilder1.Append($"{propertyInfo.Name},");
                    stringBuilder2.Append($"@{propertyInfo.Name},");
                }
            }
            string Sql = $"INSERT INTO {TableName}" + $"({stringBuilder1.ToString().TrimEnd().TrimEnd(',')}) " +
                                               $"VALUES ({stringBuilder2.ToString().TrimEnd().TrimEnd(',')})";
            return Sql;
        }

        /// <summary>
        /// 返回数据类型
        /// </summary>
        /// <returns></returns>
        public DbType GetDbType(System.Reflection.PropertyInfo property)
        {
            var ret = DbType.Object;
            if (property.PropertyType.IsGenericType && property.PropertyType.GetGenericTypeDefinition().Equals(typeof(Nullable<>)))
            {
                ret = GetDbType(property.PropertyType.GetGenericArguments()[0]);
            }
            else
            {
                ret = GetDbType(property.PropertyType);
            }

            return ret;
        }

        private DbType GetDbType(Type type)
        {
            var ret = DbType.Object;
            if (type == typeof(string))
            {
                ret = DbType.AnsiString;
            }
            else if (type == typeof(int))
            {
                ret = DbType.Int32;
            }
            else if (type == typeof(long))
            {
                ret = DbType.Int64;
            }
            else if (type == typeof(double))
            {
                ret = DbType.Double;
            }
            else if (type == typeof(float))
            {
                ret = DbType.Single;
            }
            else if (type == typeof(DateTime))
            {
                ret = DbType.DateTime;
            }
            else if (type == typeof(bool))
            {
                ret = DbType.Boolean;
            }
            else if (type == typeof(decimal))
            {
                ret = DbType.Decimal;
            }
            return ret;
        }





        /// <summary>
        /// 返回数据类型
        /// </summary>
        /// <returns></returns>
        public string GetCreateTableDbType(System.Reflection.PropertyInfo property)
        {
            string ret = "NULL";
            if (property.PropertyType == typeof(string))
            {
                ret = "TEXT";
            }
            else if (property.PropertyType == typeof(int))
            {
                ret = "INTEGER";
            }
            else if (property.PropertyType == typeof(float))
            {
                ret = "REAL";
            }
            else if (property.PropertyType == typeof(byte))
            {
                ret = "BLOB";
            }

            return ret;
        }

        /// <summary>
        /// 特性是否存在
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="member"></param>
        /// <returns></returns>
        public static bool IsAttri<T>(MemberInfo member)
        {
            foreach (object attribute in member.GetCustomAttributes(true)) //2.通过映射，找到成员属性上关联的特性类实例，
            {
                if (attribute is T)//3.如果找到了限定长度的特性类对象，就用这个特性类对象验证该成员
                {
                    return true;
                }
                else
                {
                    return false;
                }
            }
            return false;
        }

        /// <summary>
        /// 特性是否存在
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="member"></param>
        /// <returns></returns>
        public static string IsDefualValue(MemberInfo member)
        {
            string ret = string.Empty;
            foreach (object attribute in member.GetCustomAttributes(true)) //2.通过映射，找到成员属性上关联的特性类实例，
            {
                if (attribute is DefaultAttribute)//3.如果找到了限定长度的特性类对象，就用这个特性类对象验证该成员
                {
                    DefaultAttribute attr = attribute as DefaultAttribute;
                    if (attr.DefInt != -987654321)
                        return "DEFAULT " + attr.DefInt.ToString();
                    if (attr.DefFloat != -987654321)
                        return "DEFAULT " + attr.DefFloat.ToString();
                    if (attr.DefText != string.Empty)
                        return "DEFAULT " + attr.DefText.ToString();
                }
            }
            return ret;
        }
        /// <summary>
        /// 默认值
        /// </summary>
        [AttributeUsage(AttributeTargets.Class | AttributeTargets.Property | AttributeTargets.Field, AllowMultiple = false)]
        public class DefaultAttribute : Attribute
        {
            public DefaultAttribute()
            {

            }

            public string DefText { get; set; }

            public int DefInt { get; set; } = -987654321;

            public float DefFloat { get; set; } = -987654321;
        }

        /// <summary>
        /// 主键
        /// </summary>
        [AttributeUsage(AttributeTargets.Class | AttributeTargets.Property | AttributeTargets.Field, AllowMultiple = false)]
        public class PrimartyKeyAttribute : Attribute
        {
            public PrimartyKeyAttribute()
            {

            }
        }

        /// <summary>
        /// 不允许为null
        /// </summary>
        [AttributeUsage(AttributeTargets.Class | AttributeTargets.Property | AttributeTargets.Field, AllowMultiple = false)]
        public class AllowNoNULLAttribute : Attribute
        {
            public AllowNoNULLAttribute()
            {

            }

            public bool IsNoAllowNULL { get; set; } = true;
        }

    }
}
